package com.ryf.maven.dao;

import com.ryf.maven.entity.Score;
import com.ryf.maven.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description
 * @ClassName ScoreDao
 * @Author L-Cohen
 * @date 2020.12.04 14:27
 */
public class ScoreDao {

    public static Score getScore(ResultSet resultSet) throws SQLException {
        String stuNo = resultSet.getString("stuNo");
        String courseNo = resultSet.getString("courseNo");
        String courseName = resultSet.getString("courseName");
        String stuName = resultSet.getString("stuName");
        String term = resultSet.getString("term");
        String score = resultSet.getString("score");

        Score score1 = new Score();
        score1.setStuNo(stuNo);
        score1.setCourseNo(courseNo);
        score1.setCourseName(courseName);
        score1.setStuName(stuName);
        score1.setTerm(term);
        score1.setScore(score);

        return score1;
    }

    public static List<Score> getScore(int page, int limit){
        List<Score> scores = new ArrayList<Score>();
        Connection connection = DBUtil.getConnection();
        String sql = "select * from score limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareCall(sql);
            statement.setInt(1, (page - 1) * limit);
            statement.setInt(2, limit);
            resultSet = statement.executeQuery();

            while (resultSet.next()){
                Score score = getScore(resultSet);
                scores.add(score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return scores;
    }

    public static Score getScore(String stuNo, String courseNo){
        Connection connection = DBUtil.getConnection();
        String sql = "select * from score where stuNo = ? and courseNo = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Score score = null;
        try {
            statement = connection.prepareCall(sql);
            statement.setString(1, stuNo);
            statement.setString(2, courseNo);

            resultSet = statement.executeQuery();
            if (resultSet != null && resultSet.next()){
                score = getScore(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return score;
    }

    public static int delScoreByStuCou(String stuNo, String courseNo){
        Connection connection = DBUtil.getConnection();
        String sql = "delete from score where stuNo = ? and courseNo = ?";
        PreparedStatement statement = null;
        int rows = 0;
        try {
            statement = connection.prepareCall(sql);
            statement.setString(1, stuNo);
            statement.setString(2, courseNo);

            rows = statement.executeUpdate();
            DBUtil.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int updateScore(Score score){
        Connection connection = DBUtil.getConnection();
        String sql = "update score set courseName = ?, stuName = ?, term = ?, score = ? where stuNo = ? and courseNo = ?";
        PreparedStatement statement = null;
        int rows = 0;
        try {
            statement = connection.prepareCall(sql);
            statement.setString(1, score.getCourseName());
            statement.setString(2, score.getStuName());
            statement.setString(3, score.getTerm());
            statement.setString(4, score.getScore());
            statement.setString(5, score.getStuNo());
            statement.setString(6, score.getCourseNo());

            rows = statement.executeUpdate();
            DBUtil.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int addScore(Score score){
        Connection connection = DBUtil.getConnection();
        String sql = "insert into score (stuNo, courseNo, courseName, stuName, term, score) values(?,?,?,?,?,?)";
        PreparedStatement statement = null;
        int rows = 0;
        try {
            statement = connection.prepareCall(sql);
            statement.setString(1, score.getStuNo());
            statement.setString(2, score.getCourseNo());
            statement.setString(3, score.getCourseName());
            statement.setString(4, score.getStuName());
            statement.setString(5, score.getTerm());
            statement.setString(6, score.getScore());

            rows = statement.executeUpdate();
            DBUtil.close(null, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static int getCount(){
        Connection connection = DBUtil.getConnection();
        String sql = "select count(*) from  score";
        int rows = 0;
        try {
            PreparedStatement statement = connection.prepareCall(sql);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next())
            {
                rows = resultSet.getInt(1);
            }
            DBUtil.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rows;
    }

    public static void main(String[] args) {
        Score score = getScore("20066", "ke-001");
        System.out.println(score.getStuName());
    }
}
